﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.Objects;
using System.Xml;
using GAE_PI.Exceptions;
using System.Security.Cryptography;
using System.Text;

namespace GAE_PI.Models
{
    public class DBModel
    {
        private static DBModel INSTANCE;
        private static string CONNECTION_STRING_WEB;
        private static string CONNECTION_STRING_ELEVE;
        private static string CONNECTION_STRING_ADMIN;

        static DBModel()
        {
            CONNECTION_STRING_ADMIN = getConStrSQL("gae_admin","admin");
            CONNECTION_STRING_WEB = getConStrSQL("gae_web", "web");
            CONNECTION_STRING_ELEVE = getConStrSQL("gae_eleve", "eleve");
        }

        private static string getConStrSQL(string u, string p)
        {
            string connectionString = new System.Data.EntityClient.EntityConnectionStringBuilder
            {
                Metadata = "res://*/GAEModel.csdl|res://*/GAEModel.ssdl|res://*/GAEModel.msl",
                Provider = "System.Data.SqlClient",
                ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
                {
                    InitialCatalog = "gaedb",
                    DataSource = "localhost\\sqlexpress",
                    IntegratedSecurity = false,
                    UserID = u,                
                    Password = p,
                }.ConnectionString
            }.ConnectionString;
            return connectionString;
        }

        public List<sp_selectCatalogues_Result> getCatalogue()
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<sp_selectCatalogues_Result> cata = e.sp_selectCatalogues();
                List<sp_selectCatalogues_Result> list = cata.ToList();
                if (HttpContext.Current.Session["catalogue"] == null)
                {
                    HttpContext.Current.Session["catalogue"] = list.Last().dateDebut.ToShortDateString();
                }
                return list;
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }  
        }


        public void setCurrentCatalogueString(string catalogue)
        {
            HttpContext.Current.Session["catalogue"] = catalogue;
        }

        public Catalogue getCurrentCatalogue()
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<Catalogue> cata = e.sp_selectCatalogue(getSelectedDate());
                Catalogue list = cata.ToList().First();
                return list;
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public string getCatalogueState()
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<Catalogue> cata = e.sp_selectCatalogue(getSelectedDate());
                List<Catalogue> list = cata.ToList();
                return list.First().etat;
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public List<View_Activites> getActivites()
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<View_Activites> activi = e.sp_selectActivites(getSelectedDate());
                return activi.ToList();
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }  
        }

        /*
         * true = inscrit, false = pas inscrit
         */
        public int getInscriptionState(String nomA)
        {
            if (!isEleve()) return 0;
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<int?> inscrit = e.sp_selectEtatInscription(getSelectedDate(), nomA, getCurrentUser());
                if (System.Convert.ToInt32(inscrit.ToList().First()) == 1) return 1;
                return 2;
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }


        public byte[] encodeMD5(String content)
        {
            Byte[] originalBytes;
            Byte[] encodedBytes;
            MD5 md5;

            md5 = new MD5CryptoServiceProvider();
            originalBytes = ASCIIEncoding.Default.GetBytes(content);
            encodedBytes = md5.ComputeHash(originalBytes);

            //Convert encoded bytes back to a 'readable' string
            return encodedBytes;
        }

        public DetailActivityModel getDetailForActivite(String nomA, bool computeHash)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<ActiviteCatalogue> a = e.sp_selectActivite(getSelectedDate(), nomA);

                List <ActiviteCatalogue> list = a.ToList();
                if (list.Count == 0)
                {
                    throw new GAEException("Pas de " + nomA + " pour le catalogue " +getSelectedDate().ToShortDateString());
                }
                ActiviteCatalogue ac = list.First();
                DetailActivityModel current = new DetailActivityModel();
                current.Nom = ac.nomA;
                ac.detailActiviteXML.GetHashCode();
                if (ac != null)
                {
                    XmlDocument details = new XmlDocument();
                    details.LoadXml(ac.detailActiviteXML);
                    XmlNodeList dates = details.SelectSingleNode("//datesActivite").ChildNodes;
                    XmlNodeList images = details.SelectSingleNode("//images").ChildNodes;
                    if (computeHash)
                        current.HashMD5 = encodeMD5(ac.detailActiviteXML);
                    foreach (XmlNode date in dates)
                    {
                        current.Dates.Add(date.InnerText);
                    }
                    foreach (XmlNode img in images)
                    {
                        current.Images.Add(img.InnerText);
                    }

                    XmlNode root = details.SelectSingleNode("//detailActivite");
                    current.MaxInscription = System.Convert.ToInt32(root.Attributes.GetNamedItem("maxInscription", "").InnerText);
                    current.MinInscription = System.Convert.ToInt32(root.Attributes.GetNamedItem("minInscription", "").InnerText);
                    current.Prix = System.Convert.ToDouble(root.Attributes.GetNamedItem("prix", "").InnerText);
                    current.Etat = root.Attributes.GetNamedItem("etat", "").InnerText;

                    XmlNode responsables = details.SelectSingleNode("//responsables");
                    current.Responsables = responsables.InnerText;
                    XmlNode description = details.SelectSingleNode("//description");
                    current.Description = description.InnerText;
                }

                current.InscriptionState = getInscriptionState(nomA);
                return current;
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public List<sp_selectEleves_Result> getEleves()
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<sp_selectEleves_Result> activi = e.sp_selectEleves(getSelectedDate());
                return activi.ToList();
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public List<View_ActiviteEleve> getActiviteEleve(string username)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<View_ActiviteEleve> activi = e.sp_filtrerActivites(getSelectedDate(), username);
                return activi.ToList();
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public List<View_Participants> getParticipants(string nomA)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<View_Participants> participants = e.sp_selectParticipants(getSelectedDate(), nomA);
                return participants.ToList();
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public List<View_ActiviteEleve> getActiviteCurrentEleve()
        {
            return getActiviteEleve(getCurrentUser());
        }


        public String createXMLFromDetail(DetailActivityModel d)
        {
            GAEEntities e = getEntitiesContext();
            XmlDocument details = new XmlDocument();
            XmlDeclaration dec = details.CreateXmlDeclaration("1.0", "UTF-8", null);
            details.AppendChild(dec);// Create the root element

            XmlElement detail = details.CreateElement("detailActivite");
            details.AppendChild(detail);

            detail.SetAttribute("maxInscription", System.Convert.ToString(d.MaxInscription));
            detail.SetAttribute("minInscription", System.Convert.ToString(d.MinInscription));
            detail.SetAttribute("prix", System.Convert.ToString(d.Prix));
            detail.SetAttribute("etat", "ouvert");

            XmlElement desc = details.CreateElement("description");
            desc.InnerText = d.Description;
            detail.AppendChild(desc);

            XmlElement dates = details.CreateElement("datesActivite");
            foreach (String date in d.Dates)
            {
                XmlElement xDate = details.CreateElement("dateActivite");
                xDate.InnerText = date;
                dates.AppendChild(xDate);
            }
            detail.AppendChild(dates);

            XmlElement resp = details.CreateElement("responsables");
            resp.InnerText = d.Responsables;
            detail.AppendChild(resp);

            XmlElement images = details.CreateElement("images");
            foreach (String img in d.Images)
            {
                XmlElement xImg = details.CreateElement("image");
                xImg.InnerText = img;
                images.AppendChild(xImg);
            }
            detail.AppendChild(images);
            return detail.OuterXml;
        }

        public void nouvelleActivite(DetailActivityModel d)
        {
            GAEEntities e = getEntitiesContext();

            try
            {
                e.sp_insertActivite(getSelectedDate(), d.Nom, createXMLFromDetail(d));
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }


        internal void modifierActivite(DetailActivityModel model)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                e.sp_updateActivite(getSelectedDate(), model.Nom, createXMLFromDetail(model),model.HashMD5);
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        internal void modifierCatalogue(Catalogue model, Byte[] hash)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                e.sp_updateCatalogue(getSelectedDate(),model.dateDebut,model.dateOuvertureInscription,model.dateFermetureInscription,model.dateFin,model.maxActiviteEleve,hash);
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        internal void supprimerActivite(string nomA)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                e.sp_deleteActiviteCatalogue(getSelectedDate(), nomA);
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        private GAEException handleSGBDException(EntityCommandExecutionException ex)
        {
            GAEException gaex;
            if (ex.InnerException is SqlException)
            {
                SqlException sqlEx = (SqlException)ex.InnerException;
                gaex = new GAEException(sqlEx);
            }
            else
            {
                gaex = new GAEException(ex.Message);
            }
            return gaex;
        }


        public void nouveauCatalogue(Catalogue c)
        {
            GAEEntities e = getEntitiesContext();
            DateTime dd = new DateTime(c.dateDebut.Year, c.dateDebut.Month, c.dateDebut.Day);
            DateTime df = new DateTime(c.dateFin.Year, c.dateFin.Month, c.dateFin.Day);
            DateTime doi = new DateTime(c.dateOuvertureInscription.Year, c.dateOuvertureInscription.Month, c.dateOuvertureInscription.Day);
            DateTime dfi = new DateTime(c.dateFermetureInscription.Year, c.dateFermetureInscription.Month, c.dateFermetureInscription.Day);
            try
            {
                e.sp_insertCatalogue(dd, doi, dfi, df, c.maxActiviteEleve);
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public string getValueOfCatalogue()
        {
            return (string) HttpContext.Current.Session["catalogue"];
        }

        public DateTime getSelectedDate()
        {
            if (HttpContext.Current.Session["catalogue"] == null)
            {
                getCatalogue();
            }
            return Convert.ToDateTime(HttpContext.Current.Session["catalogue"]);
        }

        public void inscription(String nomA)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                e.sp_insertParticipant(getSelectedDate(), nomA, getCurrentUser());
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public void desinscription(String nomA)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                e.sp_deleteParticipant(getCurrentUser(), getSelectedDate(), nomA);
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public byte[] getHashDetailActivite(String nomA)
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<byte[]> d= e.sp_selectHashDetailActivite(getSelectedDate(),  nomA);
                return d.First();
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }

        public byte[] getHashCatalogue()
        {
            GAEEntities e = getEntitiesContext();
            try
            {
                ObjectResult<byte[]> d = e.sp_selectHashCatalogue(getSelectedDate());
                return d.First();
            }
            catch (EntityCommandExecutionException ex)
            {
                throw handleSGBDException(ex);
            }
        }
        private string getCurrentUser()
        {
            return (HttpContext.Current.Session["username"].ToString());
        }

        public static DBModel getDBM()
        {
            if (INSTANCE == null)
            {
                INSTANCE = new DBModel();
            }
            return INSTANCE;
        }

        public GAEEntities getEntitiesContext()
        {
            GAEEntities g= new GAEEntities(connectionStringForRole());
            //GAEEntities g = new GAEEntities();
            return g;
            //return new GAEEntities();
        }


        private String connectionStringForRole()
        {
            string role = (string)HttpContext.Current.Session["userrole"];
            if(role!=null)
                switch (role)
                {
                    case "0": return CONNECTION_STRING_ADMIN;
                    case "1": return CONNECTION_STRING_ELEVE;
                }
            return CONNECTION_STRING_WEB;
        }

        public bool isAdmin(){
            return HttpContext.Current.Session["userrole"] != null && HttpContext.Current.Session["userrole"].Equals("0");
        }

        public bool isEleve()
        {
            return HttpContext.Current.Session["userrole"] != null && HttpContext.Current.Session["userrole"].Equals("1");
        }


        public bool authentification(MyLogOnModel model)
        {
            GAEEntities e = getEntitiesContext();
            ObjectResult<sp_authentification_Result> authentification = e.sp_authentification(model.UserName, model.Password);
            List<sp_authentification_Result> authentificationList = authentification.ToList();
            if (authentificationList.Count > 0)
            {
                HttpContext.Current.Session["username"] = model.UserName;
                HttpContext.Current.Session["nom"] = authentificationList.First().nom;
                HttpContext.Current.Session["prenom"] = authentificationList.First().prenom;
                HttpContext.Current.Session["userrole"] = Convert.ToString(authentificationList.First().userrole);
                return true;
            }
            throw new GAEException("User/mdp incorrect");
        }

        public XmlElement createXML(IEnumerable<View_ActiviteEleve> aa)
        {
            XmlDocument details = new XmlDocument();
            XmlDeclaration dec = details.CreateXmlDeclaration("1.0", "UTF-8", null);
            details.AppendChild(dec);



            XmlElement root = details.CreateElement("Personne");
            details.AppendChild(root);

            XmlElement username = details.CreateElement("Username");
            username.InnerText = aa.First().username;
            root.AppendChild(username);

            double? theprixTot = 0.0;
            foreach (View_ActiviteEleve a in aa)
            {
                XmlElement detail = details.CreateElement("Activite");
                root.AppendChild(detail);

                XmlElement el = details.CreateElement("Nom");
                el.InnerText = a.nomA;
                detail.AppendChild(el);

                el = details.CreateElement("Dates");
                el.InnerText = "du " + a.pStart + " au" + a.pEnd;
                detail.AppendChild(el);

                el = details.CreateElement("Prix");
                el.InnerText = System.Convert.ToString(a.prix);
                detail.AppendChild(el);

                theprixTot+= a.prix;
            }

            XmlElement prixtot = details.CreateElement("PrixTot");
            root.AppendChild(prixtot);

            XmlElement pvalue = details.CreateElement("value");
            pvalue.InnerText = System.Convert.ToString(theprixTot);
            prixtot.AppendChild(pvalue);


            return root;
        }

    }
}